package com.opennetexam.manager.sqlprovider;

import org.apache.log4j.Logger;
import org.springframework.util.StringUtils;

import java.util.Map;

/**
 * @author 奶茶
 * Created on 2017/11/16.
 */
public class ExamineeProvider {

    private static Logger logger = Logger.getLogger(ExamineeProvider.class);

    public String findExamineesByCardNo(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        stringBuffer.append("SELECT * FROM Examinee ");
        if(parameter.get("examCardNo") != null){
            stringBuffer.append("WHERE ExamCardNo = \'").append((String)parameter.get("examCardNo")).append("\'");
        }

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public static String findExamClientData(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();

        String tableName = ((String) parameter.get("tableName")).trim();//查询的表名
        String examineeId = (String) parameter.get("examineeId");//考生标识
        String serviceTime = (String) parameter.get("serviceTime");//服务器时间
        Integer testPaperCreated = (Integer) parameter.get("testPaperCreated");//已生成试卷
        Integer submited = (Integer) parameter.get("submited");//已交卷
        String appId = (String) parameter.get("appId");//主办方标识
        String planId = (String) parameter.get("planId");//计划标识
        String subjectId = (String) parameter.get("subjectId");//科目标识
        String examCardNo = (String) parameter.get("examCardNo");//准考证号

        stringBuffer.append("SELECT ").append(tableName).append(".* \n" +
                "FROM Examinee examinee \n" +
                "INNER JOIN ExamPlanInfo examPlan ON examinee.ExamPlanInfoID = examPlan.ExamPlanInfoID   \n" +
                "INNER JOIN ExamingMonitor examMonitor ON examinee.ExamineeID = examMonitor.ExamineeID \n" +
                "INNER JOIN ExamineeArrange examArrange ON examinee.ExamineeID = examArrange.ExamineeID \n" +
                "INNER JOIN ExamSubject examSubject ON examMonitor.ExamSubjectID = examSubject.ExamSubjectID \n" +
                "INNER JOIN ExamScenes examScene ON examMonitor.ExamSceneID = examScene.ExamSceneID \n" +
                "INNER JOIN ExamRoom examRoom ON examMonitor.ExamRoomID = examRoom.ExamRoomID \n" +
                "INNER JOIN ExamSite examSite ON examinee.ExamSiteID = examSite.ExamSiteID \n" +
                "WHERE examinee.AppID = examPlan.AppID AND \n" +
                "examPlan.AppID = examMonitor.AppID AND \n" +
                "examMonitor.AppID = examArrange.AppID AND \n" +
                "examArrange.AppID = examSubject.AppID AND \n" +
                "examSubject.AppID = examScene.AppID AND \n" +
                "examScene.AppID = examRoom.AppID AND \n" +
                "examinee.ExamSiteID = examPlan.ExamSiteID AND \n" +
                "examPlan.ExamSiteID = examRoom.ExamSiteID AND \n" +
                "examPlan.ExamPlanInfoID = examMonitor.ExamPlanInfoID AND \n" +
                "examMonitor.ExamPlanInfoID = examArrange.ExamPlanInfoID AND \n" +
                "examArrange.ExamPlanInfoID = examSubject.ExamPlanInfoID AND \n" +
                "examSubject.ExamPlanInfoID = examScene.ExamPlanInfoID AND \n" +
                "examArrange.ExamSubjectID = examSubject.ExamSubjectID AND \n" +
                "examArrange.ExamSceneID = examScene.ExamSceneID AND \n" +
                "examArrange.ExamRoomID = examRoom.ExamRoomID \n");
        stringBuffer.append(" AND examPlan.SysState = ").append(testPaperCreated).append(" \n");
        stringBuffer.append(" AND examPlan.BeginTime < \'").append(serviceTime).append("\' \n");
        stringBuffer.append(" AND examPlan.EndTime >  \'").append(serviceTime).append("\' \n");
        stringBuffer.append(" AND examMonitor.SysState < ").append(submited).append(" \n");

        if(examineeId != null && !("").equals(examineeId)){
            stringBuffer.append(" AND examinee.ExamineeID = \'").append(examineeId).append("\' \n");
        }

        if(!StringUtils.isEmpty(appId)){
            stringBuffer.append(" AND examinee.AppID = \'").append(appId).append("\' \n");
        }

        if(planId != null && !("").equals(planId)){
            stringBuffer.append(" AND examPlan.ExamPlanInfoID = \'").append(planId).append("\' \n");
        }

        if(subjectId != null && !("").equals(subjectId)){
            stringBuffer.append(" AND examSubject.ExamSubjectID = \'").append(subjectId).append("\' \n");
        }

        if(!StringUtils.isEmpty(examCardNo)){
            stringBuffer.append(" AND LOWER(examinee.ExamCardNo) = \'").append(examCardNo).append("\' \n");
        }

        stringBuffer.append(" ORDER BY examPlan.ExamPlanType");

        String sql = stringBuffer.toString();
        return sql;
    }
}
